using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using SQLDAL;
using System.Data.SqlClient;

/// <summary>
/// Summary description for TableBookingBll
/// All method for Booking Table of restaurant
/// By: Phuong
/// Date: 4/5/2009
/// </summary>
public class BookingTableBll
{
    string sp_ByDate = "sp_BookingTable_ListAllBookingTableOfRestaurant_ByDate";
    string sp_ByMonth = "sp_BookingTable_ListAllBookingTableOfRestaurant_ByMonth";
    string sp_ByYear = "sp_BookingTable_ListAllBookingTableOfRestaurant_ByYear";

    public BookingTableBll()
	{
    }

    #region List all table booking of restaurants order by dateOrder
    public SqlDataReader ListAllTableBooking()
    {
        return SQLHelper.ExecuteReader(SQLHelper.CONN_STRING,
            CommandType.StoredProcedure, "sp_BookingTable_ListAllTableBookingInfo", null);
    }
    #endregion

    #region List all table booking of restaurants by dateOrder
    public SqlDataReader ListAllTableBookingOfRestaurantByDate(BookingTableDetailsInfo bookingTableDetailsInfo, 
        RestaurantInfo resInfo)
    {
        SqlParameter[] parms = 
            {
                new SqlParameter("@dateOrder",SqlDbType.DateTime),
                new SqlParameter("@resID", SqlDbType.VarChar)
            };
        parms[0].Value = bookingTableDetailsInfo.DateBooking;
        parms[1].Value = resInfo.ResID;
        return SQLHelper.ExecuteReader(SQLHelper.CONN_STRING, CommandType.StoredProcedure, sp_ByDate, parms);
    }
    #endregion
   
    #region List all table booking of restaurants order by month

    public SqlDataReader ListAllTableBookingOfRestaurantByMonth(int month,
        RestaurantInfo resInfo)
    {
        SqlParameter[] parms = 
            {
                new SqlParameter("@month",SqlDbType.Int),
                new SqlParameter("@resID", SqlDbType.VarChar)
            };
        parms[0].Value = month;
        parms[1].Value = resInfo.ResID;
        return SQLHelper.ExecuteReader(SQLHelper.CONN_STRING, CommandType.StoredProcedure, sp_ByMonth, parms);
    }
    #endregion    
    
    #region List all table booking of restaurants order by year
    public SqlDataReader ListAllTableBookingOfRestaurantByYear(int year,
        RestaurantInfo resInfo)
    {
        SqlParameter[] parms = 
            {
                new SqlParameter("@year", SqlDbType.Int),
                new SqlParameter("@resID", SqlDbType.VarChar)
            };
        parms[0].Value = year;
        parms[1].Value = resInfo.ResID;

        return SQLHelper.ExecuteReader(SQLHelper.CONN_STRING, CommandType.StoredProcedure, sp_ByYear, parms);
    }
    #endregion

    #region Create new table booking
    public int CreateTableBooking(BookingTableInfo bookingTableInfo)
    {
        SqlParameter[] parms = 
            {
                new SqlParameter("@bookingID", SqlDbType.VarChar,5),
                new SqlParameter("@customerName", SqlDbType.VarChar, 50),
                new SqlParameter("@email", SqlDbType.VarChar, 50),
                new SqlParameter("@phone", SqlDbType.VarChar, 12),
                new SqlParameter("@acc_no", SqlDbType.VarChar, 20)
            };
        parms[0].Value = bookingTableInfo.BookingID;
        parms[1].Value = bookingTableInfo.CustomerName;
        parms[2].Value = bookingTableInfo.Email;
        parms[3].Value = bookingTableInfo.Phone;
        parms[4].Value = bookingTableInfo.AccNo;
        

        int result = SQLHelper.ExecuteNonQuery(SQLHelper.CONN_STRING, CommandType.StoredProcedure,
                                               "sp_BookingTable_Create", parms);
        return result;
    }
    #endregion

    #region Delete a table Booking
    public int DeleteTableBooking(BookingTableInfo bookingTableInfo)
    {
        SqlParameter[] parms = 
            {
                new SqlParameter("@bookingID", SqlDbType.VarChar,5)
            };

        parms[0].Value = bookingTableInfo.BookingID;
        int result = SQLHelper.ExecuteNonQuery(SQLHelper.CONN_STRING, CommandType.StoredProcedure,
                                                "sp_BookingTable_Delete", parms);
        return result;

    }
    #endregion
  
    #region Update a table booking
    public int UpdateTableBooking(BookingTableInfo bookingTableInfo)
    {
        SqlParameter[] parms = 
            {
                new SqlParameter("@bookingID", SqlDbType.VarChar,5),
                new SqlParameter("@customerName", SqlDbType.VarChar, 50),
                new SqlParameter("@email", SqlDbType.VarChar, 50),
                new SqlParameter("@phone", SqlDbType.VarChar, 12),
                new SqlParameter("@acc_no", SqlDbType.VarChar, 20)
            };
        parms[0].Value = bookingTableInfo.BookingID;
        parms[1].Value = bookingTableInfo.CustomerName;
        parms[2].Value = bookingTableInfo.Email;
        parms[3].Value = bookingTableInfo.Phone;
        parms[4].Value = bookingTableInfo.AccNo;

        int result = SQLHelper.ExecuteNonQuery(SQLHelper.CONN_STRING, CommandType.StoredProcedure,
                                               "sp_BookingTable_Update", parms);
        return result;
    }
    #endregion

    #region List customer by table booking
    public SqlDataReader ListCustomerByTableBooking(BookingTableInfo bookingTableInfo)
    {
        SqlParameter[] parms = { 
                new SqlParameter("@bookingID", SqlDbType.VarChar, 5)
                };
        parms[0].Value = bookingTableInfo.BookingID;
        return SQLHelper.ExecuteReader(SQLHelper.CONN_STRING, CommandType.StoredProcedure,
                                    "sp_BookingTable_ListCustomerByBooking", parms);
    }
    #endregion

    //Chinh sua 14/5/2009
    #region Method use to count total table available
    public Object ToTalTableAvailable(BookingTableDetailsInfo bookingInfo, RestaurantInfo resInfo)
    {
        SqlParameter[] cmdParms = {
            new SqlParameter("@dateUsingTable", SqlDbType.DateTime),
            new SqlParameter("@timeUsing", SqlDbType.VarChar,8),
            new SqlParameter("@resID", SqlDbType.VarChar, 10)
           
        };
        cmdParms[0].Value = bookingInfo.DateUsingTable;
        cmdParms[1].Value = bookingInfo.TimeUsing;
        cmdParms[2].Value = resInfo.ResID;

        return SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING, CommandType.StoredProcedure,
            "sp_BookingTableDetails_CountTableAvailable", cmdParms);

    }
    #endregion

}
